from DrissionPage import ChromiumPage, ChromiumOptions
from tqdm import tqdm
import os
import pandas as pd
import oracledb
import logging
from datetime import datetime
import time

log_file = 'download_log.txt'
logging.basicConfig(filename=log_file, level=logging.INFO, format='%(asctime)s - %(message)s', encoding='utf-8',
                    datefmt='%Y-%m-%d %H:%M:%S')


def get_file(shop_name, url):
    logging.info(f"登录网站{url}")
    driver.get(url)
    driver.wait(1)
    results = driver.ele('.flex align-items-center date-select').eles('.el-radio-button el-radio-button--mini')
    driver.wait(0.5)
    try:
        if driver.ele('.com-box service-box collapsed'):
            pass
        else:
            driver.ele('.collapse-handle').click()
            driver.wait(0.5)
    except:
        driver.ele('.collapse-handle').click()
        driver.wait(0.5)
    for result in results:
        if result.text == '昨日':
            result.click()
            driver.wait(2)
            driver.ele('.export-btn').click()
            driver.wait(4)
            driver.ele('.el-button el-button--primary el-button--medium').click()
            driver.wait(5)
            logging.info(f'{shop_name}的数据下载完毕')
            break


product_url = ['康比特:https://www.chanmama.com/brandRank/0999051416289.html?activeTab=promotion',
               '赛霸:https://www.chanmama.com/brandRank/0399832729167.html?activeTab=promotion',
               'Foyes1:https://www.chanmama.com/brandRank/0779461129102.html?activeTab=promotion',
               'Foyes2:https://www.chanmama.com/brandRank/0968632198555.html?activeTab=promotion',
               '训练怪兽:https://www.chanmama.com/brandRank/0426122683332.html?activeTab=promotion',
               'ON:https://www.chanmama.com/brandRank/0557272852328.html?activeTab=promotion',
               '马泰时刻1:https://www.chanmama.com/brandRank/0508792652304.html?activeTab=promotion',
               '马泰时刻2:https://www.chanmama.com/brandRank/0490383197198.html?activeTab=promotion',
               '诺特兰德:https://www.chanmama.com/brandRank/0177714913089.html?activeTab=promotion',
               '紫光优健:https://www.chanmama.com/brandRank/0951406358400.html?activeTab=promotion',
               'BD必第能量:https://www.chanmama.com/brandRank/0090400650804.html?activeTab=promotion',
               '六星:https://www.chanmama.com/brandRank/0918813857183.html?activeTab=promotion',
               '肌肉科技:https://www.chanmama.com/brandRank/0631269036331.html?activeTab=promotion',
               'ALLMAX:https://www.chanmama.com/brandRank/0258321706679.html?activeTab=promotion',
               '北欧海盗:https://www.chanmama.com/brandRank/0126821794220.html?activeTab=promotion',
               '元气码头:https://www.chanmama.com/brandRank/0848035031072.html?activeTab=promotion',
               'KEEP MOMENT:https://www.chanmama.com/brandRank/0955780661586.html?activeTab=promotion',
               "MUSCLE'S PEAK:https://www.chanmama.com/brandRank/0446977800846.html?activeTab=promotion",
               '荒野健康:https://www.chanmama.com/brandRank/0239124120348.html?activeTab=promotion',
               '科派诺:https://www.chanmama.com/brandRank/0011267941931.html?activeTab=promotion',
               '肌肉博士:https://www.chanmama.com/brandRank/0941236106312.html?activeTab=promotion',
               '海德力:https://www.chanmama.com/brandRank/0630447315839.html?activeTab=promotion',
               'Myprotein:https://www.chanmama.com/brandRank/0080318181449.html?activeTab=promotion']


def get_cmm_file():
    global driver
    co = ChromiumOptions()
    driver = ChromiumPage(co)
    try:
        driver.get('https://www.chanmama.com/login.html')
        driver.eles('.el-checkbox__inner')[1].click()
        driver.wait(0.5)
        driver.ele('.el-button el-button--primary').click()
        driver.wait(1)

    except:
        pass
    try:
        for line in tqdm(product_url):
            line = line.strip()
            print(line)
            if not line:  # 如果是空白行，跳过
                continue
            shop_name = line.split(':')[0]
            logging.info(f'开始{shop_name}的数据下载')
            url = line.split(':', 1)[1].strip()
            try:
                logging.info(url)
                get_file(shop_name=shop_name, url=url)
            except:
                logging.info(f'shop_name={shop_name}, url={url}出现问题,正在重试')
                time.sleep(2)
                try:
                    driver.get('https://www.chanmama.com/login.html')
                    driver.eles('.el-checkbox__inner')[1].click()
                    driver.wait(0.5)
                    driver.ele('.el-button el-button--primary').click()
                    driver.wait(1)
                except:
                    try:
                        get_file(shop_name=shop_name, url=url)
                    except:
                        driver.wait(15)
                        get_file(shop_name=shop_name, url=url)
    except Exception as e:
        logging.info(e)
        pass
    finally:
        driver.wait(15)
        logging.info('程序结束')
        driver.quit()


def query_product_ID(product_ID):
    insert_sql = f"""
                select 品牌, 品类
            from 产品ID匹配品牌品类表 where 产品ID={product_ID}
            """
    cursor.execute(insert_sql)
    result = cursor.fetchone()
    return result


def write_data():
    product_name = ''
    folder_path = r'E:\google_download'
    files_and_folders = os.listdir(folder_path)
    for item in files_and_folders:
        item_path = os.path.join(folder_path, item)
        if os.path.isfile(item_path):
            name_list = item.split('_')
            data_time = name_list[-1].split('-')[0]
            product_name = name_list[0].split('品牌')[0][3:]
            print('品牌-->', product_name)
            print('日期-->', data_time)
            data = pd.read_excel(item_path, header=0)
            data = data.drop(index=0)
            pattern = r'id=(\d+)'
            product_ID = data['抖音商品链接'].str.extract(pattern, expand=True)
            data['产品ID'] = product_ID

            date_obj = datetime.strptime(data_time, '%Y%m%d')
            # 获取月份和日期
            month = date_obj.month
            day = date_obj.day
            # 格式化为所需的字符串格式
            formatted_date = f'{month}月{day}日'
            data['日期'] = formatted_date
            data['brand_name'] = product_name
            for index, row in data.iterrows():
                # '商品', '蝉妈妈商品链接', '抖音商品链接', '商品图片', '商品分类', '小店', '价格', '销量', '销售额',
                #        '关联达人', '关联视频', '关联直播', '产品ID', '日期', 'brand_name'
                result = query_product_ID(row['产品ID'])
                if result is None:
                    query_ID = f'''
                    select ID
from 缺失数据产品表 where 产品ID={row['产品ID']}
                    '''
                    cursor.execute(query_ID)
                    result_ID = cursor.fetchone()
                    if result_ID:
                        continue

                    logging.info(f"{row['产品ID']}数据是新品,已录入缺失数据产品表")
                    insert_sql = """
                                        INSERT INTO 缺失数据产品表 (
                                            商品, 蝉妈妈商品链接, 抖音商品链接, 商品图片, 商品分类, 小店, 价格, 销量, 销售额, 关联达人, 关联视频, 关联直播, 产品ID, 日期, brand_name, 品牌, 品类
                                        ) VALUES (
                                            :商品, :蝉妈妈商品链接, :抖音商品链接, :商品图片, :商品分类, :小店, :价格, :销量, :销售额, :关联达人, :关联视频, :关联直播, :产品ID, :日期, :brand_name, :品牌, :品类
                                        )
                                    """
                    brand, category = None, None
                else:
                    insert_sql = """
                                            INSERT INTO 数据源 (
                                                商品, 蝉妈妈商品链接, 抖音商品链接, 商品图片, 商品分类, 小店, 价格, 销量, 销售额, 关联达人, 关联视频, 关联直播, 产品ID, 日期, brand_name, 品牌, 品类
                                            ) VALUES (
                                                :商品, :蝉妈妈商品链接, :抖音商品链接, :商品图片, :商品分类, :小店, :价格, :销量, :销售额, :关联达人, :关联视频, :关联直播, :产品ID, :日期, :brand_name, :品牌, :品类
                                            )
                                        """
                    brand, category = result

                cursor.execute(insert_sql, {
                    '商品': row['商品'],
                    '蝉妈妈商品链接': row['蝉妈妈商品链接'],
                    '抖音商品链接': row['抖音商品链接'],
                    '商品图片': row['商品图片'],
                    '商品分类': row['商品分类'],
                    '小店': row['小店'],
                    '价格': row['价格'],
                    '销量': row['销量'],
                    '销售额': row['销售额'],
                    '关联达人': row['关联达人'],
                    '关联视频': row['关联视频'],
                    '关联直播': row['关联直播'],
                    '产品ID': row['产品ID'],
                    '日期': row['日期'],
                    'brand_name': row['brand_name'],
                    '品牌': brand,
                    '品类': category
                })
                # 提交事务
                connection.commit()

        logging.info(f'{product_name}数据录入完毕')
        time.sleep(1)
        os.remove(item_path)
        time.sleep(0.5)
        logging.info(f'{product_name}数据删除完毕')


def schedule_task():
    logging.info('开始录入数据')
    global connection, cursor
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    connection = oracledb.connect(user='dyyy', password='cpt123', dsn=dsn)
    cursor = connection.cursor()
    write_data()
    time.sleep(2)
    cursor.close()


if __name__ == '__main__':
    get_cmm_file()
    logging.info('文件下载完毕,两秒后将数据录入数据库')
    time.sleep(5)
    schedule_task()
    logging.info('数据录入完毕')
